<?php
/**
 * @package     Joomla.Administrator
 * @subpackage  com_users
 *
 * @copyright   Copyright (C) 2005 - 2013 Open Source Matters, Inc. All rights reserved.
 * @license     GNU General Public License version 2 or later; see LICENSE.txt
 */

defined('_JEXEC') or die;

jimport('joomla.application.component.modellist');

/**
 * Methods supporting a list of user records.
 *
 * @package     Joomla.Administrator
 * @subpackage  com_users
 * @since       1.6
 */
class UsersModelUsers extends JModelList
{
	var $_total = null;
    var $_pagination = null;

	/**
	 * Constructor.
	 *
	 * @param   array  $config  An optional associative array of configuration settings.
	 *
	 * @see     JController
	 * @since   1.6
	 */
	public function __construct($config = array())
	{
		if (empty($config['filter_fields']))
		{
			$config['filter_fields'] = array(
				'id', 'a.id',
				'name', 'a.name',
				'username', 'a.username',
				'email', 'a.email',
				'block', 'a.block',
				'sendEmail', 'a.sendEmail',
				'registerDate', 'a.registerDate',
				'lastvisitDate', 'a.lastvisitDate',
				'activation', 'a.activation',
			);
		}

		parent::__construct($config);

		$mainframe = JFactory::getApplication();
 
        // Get pagination request variables
        $limit = $mainframe->getUserStateFromRequest('global.list.limit', 'limit', $mainframe->getCfg('list_limit'), 'int');
        $limitstart = JRequest::getVar('limitstart', 0, '', 'int');
 
        // In case limit has been changed, adjust it
        $limitstart = ($limit != 0 ? (floor($limitstart / $limit) * $limit) : 0);
 
        $this->setState('limit', $limit);
        $this->setState('limitstart', $limitstart);
	}

	/**
	 * Method to auto-populate the model state.
	 *
	 * Note. Calling getState in this method will result in recursion.
	 *
	 * @return  void
	 *
	 * @since   1.6
	 */
	protected function populateState($ordering = null, $direction = null)
	{
		// Initialise variables.
		$app = JFactory::getApplication('administrator');

		// Adjust the context to support modal layouts.
		if ($layout = JRequest::getVar('layout', 'default'))
		{
			$this->context .= '.'.$layout;
		}

		// Load the filter state.
		$search = $this->getUserStateFromRequest($this->context.'.filter.search', 'filter_search');
		$this->setState('filter.search', $search);

		$active = $this->getUserStateFromRequest($this->context.'.filter.active', 'filter_active', '*');
		$this->setState('filter.active', $active);

		$state = $this->getUserStateFromRequest($this->context.'.filter.state', 'filter_state', '*');
		$this->setState('filter.state', $state);

		$groupId = $this->getUserStateFromRequest($this->context.'.filter.group', 'filter_group_id', null, 'int');
		$this->setState('filter.group_id', $groupId);

		$range = $this->getUserStateFromRequest($this->context.'.filter.range', 'filter_range');
		$this->setState('filter.range', $range);

		$groups = json_decode(base64_decode(JRequest::getVar('groups', '', 'default', 'BASE64')));
		if (isset($groups))
		{
			JArrayHelper::toInteger($groups);
		}
		$this->setState('filter.groups', $groups);

		$excluded = json_decode(base64_decode(JRequest::getVar('excluded', '', 'default', 'BASE64')));
		if (isset($excluded))
		{
			JArrayHelper::toInteger($excluded);
		}
		$this->setState('filter.excluded', $excluded);

		// Load the parameters.
		$params = JComponentHelper::getParams('com_users');
		$this->setState('params', $params);

		// List state information.
		parent::populateState('a.name', 'asc');
	}

	/**
	 * Method to get a store id based on model configuration state.
	 *
	 * This is necessary because the model is used by the component and
	 * different modules that might need different sets of data or different
	 * ordering requirements.
	 *
	 * @param   string  $id  A prefix for the store id.
	 *
	 * @return  string  A store id.
	 *
	 * @since   1.6
	 */
	protected function getStoreId($id = '')
	{
		// Compile the store id.
		$id	.= ':'.$this->getState('filter.search');
		$id	.= ':'.$this->getState('filter.active');
		$id	.= ':'.$this->getState('filter.state');
		$id	.= ':'.$this->getState('filter.group_id');
		$id .= ':'.$this->getState('filter.range');

		return parent::getStoreId($id);
	}

	/**
	 * Gets the list of users and adds expensive joins to the result set.
	 *
	 * @return  mixed  An array of data items on success, false on failure.
	 *
	 * @since   1.6
	 */
	public function getItems($filters)
	{
        $sub_sql_user = "(SELECT DISTINCT us.id as id, us.username, us.name as name, us.email as email, gr.title as group_name, gr.id as group_id, us.USEM_OCBA_CODE as ocba_code 
            FROM #__users us 
            	JOIN #__user_usergroup_map u_g ON us.id = u_g.user_id 
            	JOIN #__usergroups gr ON u_g.group_id = gr.id)";
        $sub_sql_ocba = "(SELECT DISTINCT OCBA_CODE as ocba_code, OCBA_NAME as ocba_name, PREF_CODE as ocba_pref_code, PREF_AREA_CODE as ocba_area_code
            FROM ocba
                JOIN town ON OCBA_TOWN_CODE = TOWN_CODE
                JOIN pref ON TOWN_PREF_CODE = PREF_CODE)";
    
		$sql = "SELECT * FROM $sub_sql_user as user JOIN $sub_sql_ocba as ocba ON user.ocba_code = ocba.ocba_code WHERE 1=1 ";
		$count = "SELECT COUNT(*) FROM $sub_sql_user as user JOIN $sub_sql_ocba as ocba ON user.ocba_code = ocba.ocba_code WHERE 1=1 ";
        
        $where_pref = $filters['pref'] == 'all' ? "" : "AND ocba.ocba_pref_code = $filters[pref] ";
        $where_name = $filters['ocba_name'] == '' ? "" : "AND ocba.ocba_name LIKE '%$filters[ocba_name]%' ";
        $where_type = $filters['ocba_code'] == '' ? "" : "AND ocba.ocba_code LIKE '%$filters[ocba_code]%' ";
        $where_code = $filters['type'] == 'all' ? "" : "AND LEFT(ocba.ocba_code,1) = '$filters[type]' ";
        
        /* permission */
        $where_userid = $filters['user_id'] == '' ? "" : "AND user.id = $filters[user_id] ";
        if($where_userid != ''){
            $sql.=$where_pref.$where_name.$where_type.$where_code.$where_userid;
            $count.=$where_pref.$where_name.$where_type.$where_code.$where_userid;
        }else{
            $sql.=$where_pref.$where_name.$where_type.$where_code;
            $count.=$where_pref.$where_name.$where_type.$where_code;
        }

        $sql .= " LIMIT ".$this->getState('limitstart').",".$this->getState('limit');

        if ($this->_total === null) {            
            $this->_db->setQuery($count);
            $this->_total = $this->_db->loadResult();
        }

        
        //echo "<p>$sql</p>";
        $this->_db->setQuery($sql);
        return $this->_db->loadObjectList();
	}

	function getTotal($where = null){
        // Load the content if it doesn't already exist
        if ($this->_total === null) {
            $query = "SELECT count(*) FROM kc2vr_users $where";
            $this->_db->setQuery($query);
            $this->_total = $this->_db->loadResult();
        }
        
        return $this->_total;
    } 
    
    function getPrefs(){
        $this->_db->setQuery("SELECT * FROM pref");             
        return $this->_db->loadObjectList();  
    }
	
    
    function getPagination(){
        // Load the content if it doesn't already exist
        if (empty($this->_pagination)) {
            jimport('joomla.html.pagination');
            $this->_pagination = new JPagination($this->getTotal(), $this->getState('limitstart'), $this->getState('limit') );

        }
        return $this->_pagination;
    }
    
}
